﻿Public Class frmDisplay
    Dim sql As String
    Dim dsDisplay As DataSet
    Dim daDisplay As OleDb.OleDbDataAdapter

    Private Sub frmDisplay_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'dbInventoryDataSet.tblReport' table. You can move, or remove it, as needed.
        Me.TblReportTableAdapter.Fill(Me.dbInventoryDataSet.tblReport)
        'TODO: This line of code loads data into the 'dbInventoryDataSet.tblIncome' table. You can move, or remove it, as needed.
        Me.TblIncomeTableAdapter.Fill(Me.dbInventoryDataSet.tblIncome)
        'TODO: This line of code loads data into the 'dbInventoryDataSet.tblPayment' table. You can move, or remove it, as needed.
        'Me.TblPaymentTableAdapter.Fill(Me.dbInventoryDataSet.tblPayment)
        Call gSubIconShow(Me)
        Call dbConnection()
        Call mkCmbMonth()
        Call mkReportPrint()
    End Sub

    Private Sub mkReportPrint()

        Dim dtCurrency, dtIncome, dtPayment As DataTable
        Dim preMnth, stdMnth, vStdMnth As String
        stdMnth = cmbMonth.SelectedValue.ToString
        preMnth = Mid(DateAdd("m", -1, stdMnth + "-01"), 1, 7)

        vStdMnth = Replace(stdMnth, "-", "년 ") + "월 매출/매입 보고서"

        sql = "SELECT mnth as rpt1, sum(amt) as rpt2, sum(orgAmt) as rpt3, (sum(orgAmt)-sum(amt)) as rpt4, '" + vStdMnth + "' as rpt5 " + _
                        "FROM (       " + _
            "            SELECT LEFT(incomeDt,7) as mnth, sum(orgAmount) as orgAmt, 0 as amt FROM tblIncome        " + _
            "           WHERE flag='Y' AND LEFT(incomeDt,7) IN ('" + preMnth + "','" + stdMnth + "')        " + _
            "            GROUP BY LEFT(incomeDt,7) " + _
            "            UNION ALL " + _
            "            SELECT LEFT(planDt,7) as mnth, 0 as orgAmt, sum(amount) as amt FROM tblPayment " + _
            "            WHERE flag='Y' AND LEFT(planDt,7) IN ('" + preMnth + "','" + stdMnth + "') " + _
            "            GROUP BY LEFT(planDt,7)  " + _
            ") GROUP BY mnth " + _
            "UNION ALL " + _
            "SELECT rpt1, sum(rpt02), sum(rpt03), sum(rpt04),  '" + vStdMnth + "' as rpt5  " + _
            "FROM ( " + _
            "	SELECT '증감' as rpt1, sum(amt) as rpt02, sum(orgAmt) as rpt03, (sum(orgAmt)-sum(amt)) as rpt04 " + _
            "	FROM (       " + _
            "		    SELECT LEFT(incomeDt,7) as mnth, sum(orgAmount) as orgAmt, 0 as amt FROM tblIncome        " + _
            "		    WHERE flag='Y' AND LEFT(incomeDt,7) IN ('" + stdMnth + "')        " + _
            "		    GROUP BY LEFT(incomeDt,7) " + _
            "		    UNION ALL " + _
            "		    SELECT LEFT(planDt,7) as mnth, 0 as orgAmt, sum(amount) as amt FROM tblPayment " + _
            "		    WHERE flag='Y' AND LEFT(planDt,7) IN ('" + stdMnth + "') " + _
            "		    GROUP BY LEFT(planDt,7)  " + _
            "	) GROUP BY mnth " + _
            "	UNION ALL " + _
            "	SELECT '증감' as rpt1, -1*sum(amt) as rpt02, -1*sum(orgAmt) as rpt03, -1*(sum(orgAmt)-sum(amt)) as rpt04 " + _
            "	FROM (       " + _
            "		    SELECT LEFT(incomeDt,7) as mnth, sum(orgAmount) as orgAmt, 0 as amt FROM tblIncome        " + _
            "		    WHERE flag='Y' AND LEFT(incomeDt,7) IN ('" + preMnth + "')        " + _
            "		    GROUP BY LEFT(incomeDt,7) " + _
            "		    UNION ALL " + _
            "		    SELECT LEFT(planDt,7) as mnth, 0 as orgAmt, sum(amount) as amt FROM tblPayment " + _
            "		    WHERE flag='Y' AND LEFT(planDt,7) IN ('" + preMnth + "') " + _
            "		    GROUP BY LEFT(planDt,7)  " + _
            "	) GROUP BY mnth " + _
            ") GROUP BY rpt1 "


        dtCurrency = gMkDataTable(sql, "currency")
        rptBindingSource1.DataSource = dtCurrency
        'sql = "SELECT B.company, sum(A.orgAmount) as orgAmount, sum(A.restAmount) as restAmount " + _
        '    "   FROM tblIncome A LEFT JOIN tblCompany B ON A.compCd=B.compCd " + _
        '    "   WHERE A.flag='Y' AND LEFT(incomeDt,7)='2014-04' " + _
        '    "   GROUP BY B.company ORDER BY B.company"

        'sql = "SELECT B.company, A.planDt as incomeDt, A.orgAmount, A.tax as restAmount, A.remarks " + _
        '    "   FROM tblPayment A LEFT JOIN tblCompany B ON A.compCd=B.compCd " + _
        '    "   WHERE A.flag='Y' AND LEFT(planDt,7)='2014-04' " + _
        '    "   ORDER BY A.planDt"

        sql = "SELECT A.incomeDt, B.company, A.amount, A.orgAmount, A.restAmount, A.remarks " + _
            "   FROM tblIncome A LEFT JOIN tblCompany B ON A.compCd=B.compCd " + _
            "   WHERE A.flag='Y' AND LEFT(incomeDt,7)='" + stdMnth + "' " + _
            "   ORDER BY A.incomeDt"
        dtIncome = gMkDataTable(sql, "income")
        rptBindingSource2.DataSource = dtIncome

        sql = "SELECT A.incomeDt, B.company, A.amount, A.orgAmount, A.restAmount, A.remarks " + _
            "   FROM tblIncome A LEFT JOIN tblCompany B ON A.compCd=B.compCd " + _
            "   WHERE A.flag='Y' AND LEFT(incomeDt,7)='" + stdMnth + "' " + _
            "   ORDER BY A.incomeDt"
        dtPayment = gMkDataTable(sql, "payment")
        'rptBindingSource3.DataSource = dtPayment
        Try
            Me.ReportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)

            Me.ReportViewer1.RefreshReport()
        Catch ex As NullReferenceException
            MsgBox(ex.Message)
        End Try
    End Sub


    '## 자료 보고서 작성 가능 월
    Private Sub mkCmbMonth()

        sql = "SELECT LEFT(incomeDt,7) as mnth FROM tblIncome WHERE flag='Y' GROUP BY LEFT(incomeDt,7) ORDER BY LEFT(incomeDt,7) DESC"

        Dim dsDisplay = New DataSet
        Dim daDisplay = New OleDb.OleDbDataAdapter(sql, dbConn)
        daDisplay.Fill(dsDisplay, "cmbIncMonth")

        dbConn.Close()
        With cmbMonth
            .DataSource = dsDisplay.Tables("cmbIncMonth")
            .DisplayMember = "mnth"
            .ValueMember = "mnth"
        End With
        cmbMonth.SelectedIndex = 0
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Call mkReportPrint()
    End Sub
End Class